﻿using System;
using System.Collections.Generic;
using System.Data;
using br.com.SistemaChamados.Enum;
using br.com.SistemaChamados.Model;
using br.com.SistemaChamados.QueryManager.Core;
using br.com.SistemaChamados.QueryManager.Core.Behavior;

namespace br.com.SistemaChamados.DAO
{
    public class CityDAO : GenericDao<City>
    {
        private static string SQL_ADD_CITY        = "INSERT INTO TBL_CITY (TBL_CITYDESCRIPTION,TBL_STATEID) VALUES (@citydescription,@stateid)";
        private static string SQL_LIST_CITY_STATE = "SELECT * FROM TBL_CITY WHERE TBL_STATEID = @idstate";
        private static string SQL_DELETE_CITY     = "DELETE FROM TBL_CITY WHERE TBL_CITYID = @idcity";
        private static string SQL_ALTER_CITY      = "UPDATE TBL_CITY SET TBL_CITYDESCRIPTION=@cityDescription, TBL_STATEID=@stateId WHERE TBL_CITYID=@cityId ";

        public static EnumCRUDState updateCity(City city)
        {
            EnumCRUDState resultState = EnumCRUDState.SQL_NOTHING;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_ALTER_CITY, conn);
                command.Parameters.Add(Mapped.Parameter("@cityDescription", city.Description));
                command.Parameters.Add(Mapped.Parameter("@stateId", city.State.Id));
                command.Parameters.Add(Mapped.Parameter("@cityId", city.Id));
                command.ExecuteNonQuery();
                resultState = EnumCRUDState.SQL_SUCCESS;
                return resultState;
            }
            catch (Exception ex)
            {
                return resultState;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static EnumCRUDState deleteCity(City city)
        {
            EnumCRUDState resultState = EnumCRUDState.SQL_NOTHING;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_DELETE_CITY, conn);
                command.Parameters.Add(Mapped.Parameter("@idcity", city.Id));
                command.ExecuteNonQuery();
                resultState = EnumCRUDState.SQL_SUCCESS;
                return resultState;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static List<City> listCity()
        {
            List<City> listCity = null;
            try
            {
                listCity = QueryManagerPerformer.Select(typeof(City), ConnectionBehavior.CONNECTION_BEHAVIOR_CLOSE_AFTER_OPERATION)
                            .ConvertAll(convertObject);
                return listCity;
            }
            catch (Exception ex)
            {
                listCity = null;
                return null;
                throw ex;
            }
        }

        public static List<City> listCitiesByState(int stateId)
        {
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            City city = null;
            List<City> listCity = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_LIST_CITY_STATE, conn);
                command.Parameters.Add(Mapped.Parameter("@idstate", stateId));
                reader = command.ExecuteReader();
                listCity = new List<City>();
                while (reader.Read())
                {
                    city = new City();
                    city.Id = Convert.ToInt32(reader["TBL_CITYID"]);
                    city.Description = Convert.ToString(reader["TBL_CITYDESCRIPTION"]);
                    State state = new State();
                    state.Id = stateId;
                    city.State = state;
                    listCity.Add(city);
                }
                return listCity;
            }
            catch (Exception ex)
            {
                listCity = null;
                return null;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static City SelectCity(int cityId)
        {
            City newCity = null;
            try
            {
                newCity = (City)QueryManagerPerformer.Select(typeof(City), 
                                                             "Id", 
                                                             cityId, 
                                                             ConnectionBehavior.CONNECTION_BEHAVIOR_CLOSE_AFTER_OPERATION);
                return newCity;
            }
            catch (Exception ex)
            {
                newCity = null;
                return null;
                throw ex;
            }
        }


        public static EnumCRUDState addCity(City city)
        {
            EnumCRUDState resultState = EnumCRUDState.SQL_NOTHING;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_ADD_CITY, conn);
                command.Parameters.Add(Mapped.Parameter("@citydescription", city.Description));
                command.Parameters.Add(Mapped.Parameter("@stateid", city.State.Id));
                command.ExecuteNonQuery();
                resultState = EnumCRUDState.SQL_SUCCESS;
            }
            catch (Exception ex)
            {
                resultState = EnumCRUDState.SQL_ERROR;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return resultState;
        }
    }
}